﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Elektronix.Class
{
    public class productclass
    {
       
        public int productID { get; set; }
        public string productName { get; set; }
        public string model { get; set; }
        public double price{ get; set; }
        public int categoryID { get; set; }
        public int supplierID { get; set; }  //marka ID
        public string brand { get; set; }  //marka Adı
        public long stock { get; set; }
        public int unitsonorder { get; set; }
        public int reorderlevel { get; set; }  //kritik stok seviyesi
        public double discount { get; set; }
        public int status { get; set; }

        public string status_adi { get; set; }
        public string adddate { get; set; }

        public string categoryname { get; set; }
        public string photopath { get; set; }

        public int resimID { get; set; }
        public string detaylisorgu { get; set; }


        public productclass()
        {

        }

        public void urungetirIDli()
        {
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p left join resimler as r on p.productID=r.productID where p.productID=@pid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@pid", productID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productName = sdr["productname"].ToString();
                model = sdr["model"].ToString();
                price = Convert.ToDouble(sdr["price"]);
                categoryID = Convert.ToInt32(sdr["categoryID"]);
                supplierID = Convert.ToInt32(sdr["supplierID"]);
                brand = sdr["brand"].ToString();
                stock = Convert.ToUInt32(sdr["stock"]);
                unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                discount = Convert.ToDouble(sdr["discount"]);
                status = Convert.ToInt32(sdr["status"]);
                photopath = sdr["photopath"].ToString();
                adddate = sdr["adddate"].ToString();
            }
            sqlcon.Close();
        }



        public List<productclass> urunlergetircatIDli()
        {
            List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p inner join categories as c on p.categoryID=c.categoryID right join resimler as r on p.productID=r.productID  where p.categoryID=@cid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@cid", categoryID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productclass p = new productclass();
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.categoryname = sdr["categoryname"].ToString();
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                p.photopath = sdr["photopath"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }


        public List<productclass> urunlergetirdetaylisorgu()
        {
            List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand(detaylisorgu, sqlcon);
          
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productclass p = new productclass();
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                //p.categoryname = sdr["categoryname"].ToString();
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                p.photopath = sdr["photopath"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }


        public void kaydet()
        {
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("insert into products values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12)",sqlcon);
            sqlcmd.Parameters.AddWithValue("@1",productName);
            sqlcmd.Parameters.AddWithValue("@2",model);
            sqlcmd.Parameters.AddWithValue("@3",price);
            sqlcmd.Parameters.AddWithValue("@4",categoryID);
            sqlcmd.Parameters.AddWithValue("@5",supplierID);
            sqlcmd.Parameters.AddWithValue("@6",brand);
            sqlcmd.Parameters.AddWithValue("@7",stock);
            sqlcmd.Parameters.AddWithValue("@8",unitsonorder);
            sqlcmd.Parameters.AddWithValue("@9",reorderlevel);
            sqlcmd.Parameters.AddWithValue("@10",discount);
            sqlcmd.Parameters.AddWithValue("@11",status);
            sqlcmd.Parameters.AddWithValue("@12",adddate);  
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
        }



        public void sil()
        {
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("delete from products where productID=@pid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@pid",productID);
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
        }



        public void guncelle()
        {
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("update products set productName=@1,model=@2,price=@3,categoryID=@4,supplierID=@5,brand=@6,stock=@7,unitsonorder=@8,reorderlevel=@9,discount=@10,status=@11 where productID=@pid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@1", productName);
            sqlcmd.Parameters.AddWithValue("@2", model);
            sqlcmd.Parameters.AddWithValue("@3", price);
            sqlcmd.Parameters.AddWithValue("@4", categoryID);
            sqlcmd.Parameters.AddWithValue("@5", supplierID);
            sqlcmd.Parameters.AddWithValue("@6", brand);
            sqlcmd.Parameters.AddWithValue("@7", stock);
            sqlcmd.Parameters.AddWithValue("@8", unitsonorder);
            sqlcmd.Parameters.AddWithValue("@9", reorderlevel);
            sqlcmd.Parameters.AddWithValue("@10", discount);
            sqlcmd.Parameters.AddWithValue("@11", status);
            sqlcmd.Parameters.AddWithValue("@pid", productID);
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
        }

        public static List<productclass> ensonurungetir()
        {
            List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select top(6) * from products order by adddate desc", sqlcon);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productclass p = new productclass();
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }



        public static List<productclass> ozelurunler()
        {
             List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products where status=2", sqlcon);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productclass p = new productclass();
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }



        public static List<productclass> durumlar()
        {
            List<productclass> drm = new List<productclass>();

            string[] dur = { "pasif", "aktif", "fırsat" };
            int[] durindex = { 0,1,2 };
            for (int i = 0; i < dur.Length; i++)
            {
                productclass p = new productclass();
                p.status = durindex[i];
                p.status_adi = dur[i];
                drm.Add(p);
            }
            return drm;
        }



        public static List<productclass> urunlergetir()
        {
            List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p inner join categories as c on p.categoryID=c.categoryID", sqlcon);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productclass p = new productclass();
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.categoryname = sdr["categoryname"].ToString();
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }




        public List<productclass> resimlerigetir()
        {
            List<productclass> prd = new List<productclass>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from resimler where productID=@ID",sqlcon);
            sqlcmd.Parameters.AddWithValue("@ID", productID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();

            while (sdr.Read())
            {
                productclass p = new productclass();
                p.photopath = sdr["photopath"].ToString();
                p.resimID = Convert.ToInt32(sdr["resimID"]);
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }

        public List<productclass> caprazsorgu()
        {
            List<productclass> lst = new List<productclass>();
            string sorgu="select * from products as p inner join categories as c on c.categoryID=p.categoryID where p.categoryID=" + categoryID.ToString() + " and supplierID=" + supplierID.ToString() + " and status=" + status.ToString() + " and price>=" + price.ToString() + " and stock >=" + stock.ToString();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand(sorgu, sqlcon);
            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();
           
      
          while (sdr.Read())
          {
              productclass p = new productclass();
              p.productName = sdr["productname"].ToString();
              p.model = sdr["model"].ToString();
              p.price = Convert.ToDouble(sdr["price"]);
              p.productID = Convert.ToInt32(sdr["productID"]);
              p.supplierID = Convert.ToInt32(sdr["supplierID"]);
              p.categoryname = sdr["categoryname"].ToString();
              p.brand = sdr["brand"].ToString();
              p.stock = Convert.ToUInt32(sdr["stock"]);
              p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
              p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
              p.discount = Convert.ToDouble(sdr["discount"]);
              p.status = Convert.ToInt32(sdr["status"]);
              p.adddate = sdr["adddate"].ToString();
              lst.Add(p);
              
          }

          sqlcon.Close();
            return lst;
        
        }




    }
}